package dao.impl;

import dao.BaseDao;
import dao.Dorm_bedsDao;
import vo.Student_checkin;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class Dorm_bedsDaoimpl extends BaseDao implements Dorm_bedsDao {

    @Override
    public int add(int roomid, String bedname, int studentid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pre = null;

        try {

            String sql = "UPDATE `student`.`dorm_beds` SET `student_id` = ? WHERE `room_id` = ? AND `bed_number` = ?";
            pre = conn.prepareStatement(sql);
            pre.setInt(1, studentid);
            pre.setInt(2, roomid);
            pre.setString(3, bedname);
            return pre.executeUpdate();
        } finally {
            if (pre != null) pre.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public int rz(int roomid) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pre = null;
        ResultSet rs = null;

        try {

            String sql = "SELECT COUNT(1) FROM `dorm_beds` WHERE room_id=?";
            pre = conn.prepareStatement(sql);
            pre.setInt(1, roomid);
            rs = pre.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            if (rs != null) rs.close();
            if (pre != null) pre.close();
            if (conn != null) conn.close();
        }
    }

    @Override
    public List<Student_checkin> cx(int room_id, String bed_number) throws SQLException, ClassNotFoundException {
        getConnection();
        PreparedStatement pre = null;
        ResultSet rs = null;
        List<Student_checkin> list = new ArrayList<>();

        try {

            String sql = "SELECT student_number FROM students a, dorm_beds b " +
                    "WHERE a.student_id = b.student_id AND b.room_id = ? AND b.bed_number = ?";
            pre = conn.prepareStatement(sql);
            pre.setInt(1, room_id);
            pre.setString(2, bed_number);
            rs = pre.executeQuery();

            while (rs.next()) {
                Student_checkin st = new Student_checkin();
                st.setStudent_number(rs.getString("student_number"));
                list.add(st);
            }
            return list;
        } finally {
            if (rs != null) rs.close();
            if (pre != null) pre.close();
            if (conn != null) conn.close();
        }
    }
}